﻿

using System;
using System.Collections.Generic;

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Profile;
using System.Data;
using System.Text;
using Gift.Model.ENUM;

using Gift.DAL;
using Gift.Model;
using Gift.Utile;


    public partial class admin_Report_CardReport : BaseAdmin
    {


        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.GridView1.PageIndex = this.ddlCurrentPage.SelectedIndex;
            this.GridViewBind("");
        }

        private string GetSql()
        {
            string sql = " select  substring({0},1,{1}) as 日期,(select count(1) from DB_Card b where 1=1 {3})  as 卡片数量,"

                                   + " sum(case states when 4  then 1 else 0 end) as 当{2}未激活数, "
                                  + " sum(case states when 2  then 1 else 0 end) as 当{2}使用数, "
                                    + " sum(case states when 1  then 1 else 0 end) as 当{2}开卡数, "
                                  + " sum(case states when 3  then 1 else 0 end) as 当{2}禁用数, "
                                  + "  isnull((select sum(1) from DB_Card b where states=1 and convert(datetime,{0}) <= convert(datetime,{0}) {3} ),0) as 已开卡数,"
                               + "  isnull( (select sum(1) from DB_Card b where states=2 and convert(datetime,{0}) <= convert(datetime,{0})  {3} ),0) as 已使用数, "
                                + " isnull((select sum(1) from DB_Card b where states=3 and convert(datetime,{0}) <= convert(datetime,{0}) {3}  ),0) as 已禁用数,  "
                                + " isnull((select sum(1) from DB_Card b where states=4 and convert(datetime,{0}) <= convert(datetime,{0}) {3}  ),0) as 未开卡数, "
                                + " isnull((select sum(1) from DB_Card b where states=1 and convert(datetime,{0}) between  convert(datetime,convert(varchar(10),endtime,121)) and  convert(datetime,convert(varchar(10),lasttime,121)) {3}),0) as 专区使用期内 "
                                + " from DB_Card c "
                                   + " group by {0} ";

            string sSelectProfession = string.Empty;
            if (!string.IsNullOrEmpty(this.ddlProfessionAreaType.SelectedItem.Value))
            {
                sSelectProfession = " and exists (select 1 from tbCardType where c.CardTypeId = Id And ProfessionAreaTypeId = " + this.ddlProfessionAreaType.SelectedItem.Value + ")";
            }
            if (!string.IsNullOrEmpty(this.ddlCardType.SelectedItem.Value))
            {
                sSelectProfession += " and  c.CardTypeId = " + this.ddlCardType.SelectedItem.Value;
            }

            string sSelectSQL = string.Empty;
            if (!string.IsNullOrEmpty(this.ddlProfessionAreaType.SelectedItem.Value))
            {
                sSelectSQL = " and exists (select 1 from tbCardType where b.CardTypeId = Id And ProfessionAreaTypeId = " + this.ddlProfessionAreaType.SelectedItem.Value + ")";
            }
            if (!string.IsNullOrEmpty(this.ddlCardType.SelectedItem.Value))
            {
                sSelectSQL += " and  b.CardTypeId = " + this.ddlCardType.SelectedItem.Value;
            }


            if (!string.IsNullOrEmpty(this.ddlMonth.SelectedItem.Value))
            {
                string sMonth = "";
                if (int.Parse(this.ddlMonth.SelectedItem.Value) < 10)
                {
                    sMonth = "0" + this.ddlMonth.SelectedItem.Value;
                }
                if (sSelectProfession != string.Empty)
                {
                    sql = sql + ",CardTypeId having {0} ='" + this.ddlYear.SelectedItem.Value + "-" + sMonth + "'";
                }
                else
                {
                    sql = sql + " having {0} ='" + this.ddlYear.SelectedItem.Value + "-" + sMonth + "'";
                }
            }
            else
            {
                if (sSelectProfession != string.Empty)
                {
                    sql = sql + ",CardTypeId having substring({0},1,4) ='" + this.ddlYear.SelectedItem.Value + "'";
                }
                else
                {
                    sql = sql + " having substring({0},1,4) ='" + this.ddlYear.SelectedItem.Value + "'";
                }
            }
            sql = sql + sSelectProfession;
            sql += " order by {0} desc";
            if (this.ddlTimeType.SelectedItem.Value == "1")
            {
                sql = string.Format(sql, "convert(varchar(10),updatetime,121)", "10", "天", sSelectSQL);
            }
            else
            {
                sql = string.Format(sql, "substring(convert(varchar(10),updatetime,121),1,7)+'-01'", "7", "月", sSelectSQL);
            }
            return sql;
        }

        private void BindProfessionAreaType()
        {
            DataSet dsType = new DataSet();
            DALProfessionAreaType theType = new DALProfessionAreaType();
            dsType = theType.GetList("");
            if (dsType.Tables.Count > 0)
            {
                this.ddlProfessionAreaType.DataSource = dsType.Tables[0].DefaultView;
                this.ddlProfessionAreaType.DataTextField = "Name";
                this.ddlProfessionAreaType.DataValueField = "Id";
                this.ddlProfessionAreaType.DataBind();
            }
            this.ddlProfessionAreaType.Items.Insert(0, new ListItem("所属专区", ""));
        }

        private void GridViewBind(string sqlSort)
        {
            string sql = GetSql();

            DataTable table = new DataTable();
            table = SQLAccess.GreatDs(sql).Tables[0];


            // 获取GridView排序数据列及排序方向
            string sortExpression = this.GridView1.Attributes["SortExpression"];
            string sortDirection = this.GridView1.Attributes["SortDirection"];
            // 根据GridView排序数据列及排序方向设置显示的默认数据视图
            if ((!string.IsNullOrEmpty(sortExpression)) && (!string.IsNullOrEmpty(sortDirection)))
            {
                table.DefaultView.Sort = string.Format("{0} {1}", sortExpression, sortDirection);
            }
          
            this.GridView1.DataSource = table.DefaultView;
            this.GridView1.DataBind();
            this.LblCurrentIndex.Text = "当前第 " + ((this.GridView1.PageIndex + 1)).ToString() + "页";
            this.LblPageCount.Text = "共 " + this.GridView1.PageCount.ToString() + "页";
            this.LblRecordCount.Text = "总计 " + table.Rows.Count.ToString() + "个记录";
            if (table.Rows.Count == 0)
            {
                this.btnFirst.Visible = false;
                this.btnPrev.Visible = false;
                this.btnNext.Visible = false;
                this.btnLast.Visible = false;
                this.LblCurrentIndex.Visible = false;
                this.LblPageCount.Visible = false;
                this.LblRecordCount.Visible = false;
            }
            else if (this.GridView1.PageCount == 1)
            {
                this.btnFirst.Visible = false;
                this.btnPrev.Visible = false;
                this.btnNext.Visible = false;
                this.btnLast.Visible = false;
            }
            this.btnFirst.CommandName = "1";
            this.btnPrev.CommandName = (this.GridView1.PageIndex == 0) ? "1" : this.GridView1.PageIndex.ToString();
            this.btnNext.CommandName = (this.GridView1.PageCount == 1) ? this.GridView1.PageCount.ToString() : ((this.GridView1.PageIndex + 2)).ToString();
            this.btnLast.CommandName = this.GridView1.PageCount.ToString();
            this.ddlCurrentPage.Items.Clear();
            if (this.GridView1.PageCount > 0)
            {
                for (int i = 1; i <= this.GridView1.PageCount; i++)
                {
                    this.ddlCurrentPage.Items.Add(i.ToString());
                }
                this.ddlCurrentPage.SelectedIndex = this.GridView1.PageIndex;
            }

            ViewChart(table);
        }


        protected void Page_Load(object sender, EventArgs e)
        {

            if (!base.IsPostBack)
            {
                this.BindProfessionAreaType();
                base.ValidateLoginRule(ERule.CardStat);
                this.BindYearAndMonth(); 

            }
        }

        protected void PagerButtonClick(object sender, EventArgs e)
        {
            this.GridView1.PageIndex = Convert.ToInt32(((LinkButton)sender).CommandName) - 1;
            this.GridViewBind("");
        }

        protected void ibtnSearch_Click(object sender, ImageClickEventArgs e)
        {
            this.GridViewBind("");
        }


        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            // 从事件参数获取排序数据列
            string sortExpression = e.SortExpression.ToString();

            // 假定为排序方向为“顺序”
            string sortDirection = "ASC";

            // “ASC”与事件参数获取到的排序方向进行比较，进行GridView排序方向参数的修改
            if (sortExpression == this.GridView1.Attributes["SortExpression"])
            {
                //获得下一次的排序状态
                sortDirection = (this.GridView1.Attributes["SortDirection"].ToString() == sortDirection ? "DESC" : "ASC");
            }

            // 重新设定GridView排序数据列及排序方向
            this.GridView1.Attributes["SortExpression"] = sortExpression;
            this.GridView1.Attributes["SortDirection"] = sortDirection;

            this.GridViewBind(string.Empty);
        }

        protected void ibtnExport_Click(object sender, ImageClickEventArgs e)
        {
            string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".csv";//设置导出文件的名称   
            string sql = GetSql();

            sql = sql + " order by 日期 desc ";

            DataTable table = new DataTable();
            table = SQLAccess.GreatDs(sql).Tables[0];
            string sTitle = string.Empty;
            if (!string.IsNullOrEmpty(this.ddlMonth.SelectedItem.Value))
            {
                sTitle = " 订单数量统计表(" + this.ddlYear.SelectedItem.Value + "年" + this.ddlMonth.SelectedItem.Value + "月)";
            }
            else
            {
                sTitle = " 订单数量统计表(" + this.ddlYear.SelectedItem.Value + "年)";
            }
            Gift.Utile.OperateExcel.ExportCSVWithTitle(table, fileName, sTitle);
        }

        void BindYearAndMonth()
        {
            for (int i = 2011; i <= 2015; i++)
            {
                this.ddlYear.Items.Add(i.ToString());
            }

            for (int i = 1; i <= 12; i++)
            {
                this.ddlMonth.Items.Add(i.ToString());
            }
            this.ddlMonth.Items.Insert(0, new ListItem("请选取月", ""));
        }

        void ViewChart(DataTable dtChart)
        {


            if (this.ddlTimeType.SelectedItem.Value == "2")
            {
                string sTitle = this.ddlYear.SelectedItem.Value + "年" + "卡片状态数量统计(月)";

                string[] xNames = new string[dtChart.Rows.Count];
                double[] yVal1 = new double[dtChart.Rows.Count];
                double[] yVal2 = new double[dtChart.Rows.Count];
                double[] yVal3 = new double[dtChart.Rows.Count];

                int m = 0;
                for (int i = dtChart.Rows.Count - 1; i >= 0; i--)
                {
                    xNames[m] = int.Parse(dtChart.Rows[i][0].ToString().Substring(5, 2)).ToString() + "月";
                    yVal1[m] = double.Parse(dtChart.Rows[i][2].ToString());
                    yVal2[m] = double.Parse(dtChart.Rows[i][3].ToString());
                    yVal3[m] = double.Parse(dtChart.Rows[i][4].ToString());
                    m++;

                }

            }
            else
            {
                string sTitle = this.ddlYear.SelectedItem.Value + "年" + "卡片状态数量统计(天)";

                string[] xNames = new string[dtChart.Rows.Count];
                double[] yVal1 = new double[dtChart.Rows.Count];
                double[] yVal2 = new double[dtChart.Rows.Count];
                double[] yVal3 = new double[dtChart.Rows.Count];

                int m = 0;
                for (int i = dtChart.Rows.Count - 1; i >= 0; i--)
                {
                    xNames[m] = dtChart.Rows[i][0].ToString();
                    yVal1[m] = double.Parse(dtChart.Rows[i][2].ToString());
                    yVal2[m] = double.Parse(dtChart.Rows[i][3].ToString());
                    yVal3[m] = double.Parse(dtChart.Rows[i][4].ToString());
                    m++;

                }

            }
        }

        protected void ddlProfessionAreaType_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(this.ddlProfessionAreaType.SelectedItem.Value))
            {
                DataSet dsType = new DataSet();
                DALCardType theType = new DALCardType();
                dsType = theType.GetList(" ProfessionAreaTypeId = " + this.ddlProfessionAreaType.SelectedItem.Value);
                if (dsType.Tables.Count > 0)
                {
                    this.ddlCardType.DataSource = dsType.Tables[0].DefaultView;
                    this.ddlCardType.DataTextField = "Name";
                    this.ddlCardType.DataValueField = "Id";
                    this.ddlCardType.DataBind();
                }
                this.ddlCardType.Items.Insert(0, new ListItem("所属卡片类型", ""));
            }
        }
    }
